﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TownCrier.Infrastructure.Contracts;
using TownCrier.Infrastructure.Entities;
using System.Data.SqlClient;
using System.Data;

namespace TownCrier.Infrastructure.Repository {
    public class SqlRepository : IRepository<Cry> {

        //private TownCrierContext _db;
        private SqlConnection _connection;

        //public SqlRepository() : this(new TownCrierContext()) { }

        public SqlRepository() : 
            this(System.Configuration.ConfigurationManager.ConnectionStrings["TownCrierDatabase"].ConnectionString) { }

        //public SqlRepository(TownCrierContext db) {
        //    _db = db;
        //}

        public SqlRepository(string conn) {
            _connection = new SqlConnection(conn);
        }

        public SqlRepository(SqlConnection conn) {
            _connection = conn;
        }

        #region IRepository<Cry> Members

        public IEnumerable<Cry> GetAll() {
            //throw new NotImplementedException();
            //return _db.Cries;
            _connection.Open();
            string sql = "select c.ID AS cryID,c.Title AS cryTitle,c.FullText AS cryText,c.StartDate,c.EndDate,c.Address,s.ID AS statID,s.Channel,s.Delivered,s.DateDelivered from [dbo].[Cries] c inner join [dbo].[Statistics] s on c.ID = s.CryID";
            SqlCommand command = _connection.CreateCommand();
            command.CommandText = sql;
            Dictionary<Guid, Cry> results = new Dictionary<Guid, Cry>();
            using (SqlDataReader dr = command.ExecuteReader()) {
                Guid seenID;
                //List<Cry> results = new List<Cry>();
                while (dr.Read()) {
                    Guid currID = dr.GetGuid(0);
                    if (results.ContainsKey(currID)) { // we have the same cry
                        Statistic temp = new Statistic {
                            Id = dr.GetInt32(6),
                            Channel = dr.GetString(7),
                            Delivered = dr.GetBoolean(8),
                            DateDelivered = dr.GetDateTime(9),
                            Cry = results[currID]
                        };
                        results[currID].Statistics.Add(temp);
                        continue;
                    }
                    Cry cry = new Cry {
                        Id = currID,
                        Title = dr.GetString(1),
                        FullText = dr.GetString(2),
                        StartDate = dr.GetDateTime(3),
                        EndDate = dr.GetDateTime(4),
                        Address = dr.GetString(5)
                    };
                    cry.Statistics = new List<Statistic> {
                        new Statistic {
                            Id = dr.GetInt32(6),
                            Channel = dr.GetString(7),
                            Delivered = dr.GetBoolean(8),
                            DateDelivered = dr.GetDateTime(9),
                            Cry = cry
                        }
                    };
                    seenID = currID;
                    //results.Add(cry);
                    results.Add(currID, cry);
                }
            }

            return results.Select<KeyValuePair<Guid, Cry>, Cry>(di => di.Value).ToList();
        }

        public Cry GetOne(Guid id) {
            throw new NotImplementedException();
            //return _db.Cries.Single(c => c.Id == id);
        }

        public IEnumerable<Cry> Filter(Func<Cry, bool> predicate) {
            throw new NotImplementedException();
            //return _db.Cries.Where(predicate).AsQueryable();
        }

        public void Add(Cry entity) {
            //throw new NotImplementedException();
            //if (entity.ID != null) {
            //    _db.Cries.Attach(entity);
            //} else {
                //_db.Cries.Add(entity);
            //}
            //_db.SaveChanges();
            _connection.Open();
            SqlTransaction transaction = _connection.BeginTransaction();
            SqlCommand command = _connection.CreateCommand();
            command.Transaction = transaction;
            try {
                command.CommandText = "INSERT INTO [Cries] (ID,Title,FullText,StartDate,EndDate,Address) VALUES(@id,@title,@fulltext,@start,@end,@address)";
                command.Parameters.AddWithValue("@id", entity.Id);
                command.Parameters.AddWithValue("@title", entity.Title);
                command.Parameters.AddWithValue("@fulltext", entity.FullText);
                command.Parameters.AddWithValue("@start", entity.StartDate);
                command.Parameters.AddWithValue("@end", entity.EndDate);
                command.Parameters.AddWithValue("@address", entity.Address);
                command.ExecuteNonQuery();
                command.CommandText = "INSERT INTO [Statistics] (Channel,Delivered,DateDelivered,Status,CryID) VALUES(@channel,@delivered,@date,@status,@cryID)";
                command.Parameters.Add("@channel", System.Data.SqlDbType.NVarChar);
                command.Parameters.Add("@delivered", SqlDbType.Bit);
                command.Parameters.Add("@date", SqlDbType.DateTime);
                command.Parameters.Add("@status", SqlDbType.Int);
                command.Parameters.Add("@cryID", SqlDbType.UniqueIdentifier);

                foreach (var item in entity.Statistics) {
                    command.Parameters["@channel"].Value = item.Channel;
                    command.Parameters["@delivered"].Value = item.Delivered;
                    command.Parameters["@date"].Value = item.DateDelivered;
                    command.Parameters["@status"].Value = item.DeliveryStatus;
                    command.Parameters["@cryID"].Value = entity.Id;
                    command.ExecuteNonQuery();

                }
                transaction.Commit();

            } catch (SqlException exception) {
                // TODO some logging here
                transaction.Rollback();
                throw exception;
            }



        }

        public void Delete(Cry entity) {
            throw new NotImplementedException("Not yet implemented");
            //_db.Cries.Remove(entity);
            //_db.SaveChanges();
            _connection.Open();
            SqlCommand command = new SqlCommand();
            command.Connection = _connection;
            command.CommandText = String.Format("DELETE FROM dbo.Cries WHERE CryID = {0}", entity.Id);

            command.ExecuteNonQuery();
        }

        #endregion
    }
}
